/*LICENSE_BEGIN
 * ******************************************************************
 * All original parts of SoccerScope2003 have copyright (2003) by
 * Team YowAI, represented by Mr. Shuhei Shiota
 * The Department of Creative Informatics
 * The Graduate School of Information Science and Technology
 * The University of Tokyo
 * 
 * Additional classes (except libraries in /lib) were developed by 
 * the Dainamite Robocup-Team of the DAI-Labor, TU-Berlin 
 * (www.dai-labor.de)
 * 
 * Extended/new classes/packages are:
 *   soccerscope.analyzer.LogfileAnalyzer
 *   soccerscope.daistatistic.*
 *   soccerscope.daistatistic.charts.*
 *   soccerscope.daistatistic.tools.*
 *   soccerscope.db.*
 *   soccerscope.dialog.*
 *   soccerscope.model.PlayerWorldModel
 *   soccerscope.model.SimplePlayer
 *   soccerscope.net.AgentConnector
 *   soccerscope.training.*
 *   soccerscope.training.conditions.*
 * ******************************************************************
LICENSE_END*/
package soccerscope.util.analyze;

import java.util.ArrayList;
import java.sql.Connection;
import soccerscope.db.DatabaseConfig;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import soccerscope.model.PlayMode;
import java.sql.ResultSet;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2006</p>
 *
 * <p>Company: </p>
 * @version 1.0
 */
public class DBAnalyzer {
    public ArrayList<Object []> analyzerList = new ArrayList<Object[]>();
    // Zugang zu Datenbank-Server
    public static Connection connection = null;
    public String sqlHost;
    public String sqlDB;
    public String sqlUser;
    public String sqlPassword;
    public DBAnalyzer() {
        DatabaseConfig dbConfig = DatabaseConfig.getInstance();
        dbConfig.load();
        sqlHost = dbConfig.getSqlHost();
        sqlDB = dbConfig.getSqlDB();
        sqlUser = dbConfig.getSqlUser();
        sqlPassword = dbConfig.getSqlPassword();

        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection("jdbc:mysql://" + sqlHost + "/" + sqlDB, sqlUser, sqlPassword);
            System.out.println("[RobocupDatabase] Database connected");
       } catch (ClassNotFoundException ex) {
           ex.printStackTrace();
        } catch (IllegalAccessException ex) {
            ex.printStackTrace();
        } catch (InstantiationException ex) {
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private String[] analyze(String name, int gameID, int playModeLeft, int playModeRight, boolean revertSort)
    {
/*        String query = "SELECT client_scene.game_id, client_scene.play_mode, client_scene.time, client_scene.id " +
                             "FROM client_scene WHERE " +
                             "(client_scene.game_id = ?) AND (client_scene.play_mode = ?) GROUP BY time";
 */
String query = "SELECT client_scene.game_id, client_scene.play_mode, client_scene.time, client_scene.id, client_scene.ball_pos_y, client_scene.ball_pos_x " +
                                   "FROM client_scene WHERE " +
                                   "(client_scene.game_id = ?) AND (client_scene.play_mode = ?) GROUP BY ball_pos_x, ball_pos_y";

                    if (revertSort)
                        query += " ORDER BY client_scene.time DESC";
        int leftCount = 0, rightCount = 0;
        String leftTime = "", rightTime = "";

        try {
            PreparedStatement stm = connection.prepareStatement(query);
            stm.setInt(1, gameID);
            stm.setInt(2, playModeLeft);

            ResultSet rs = stm.executeQuery();
            while (rs.next())
            {
                leftCount++;
                if (leftTime.compareTo("") != 0)
                    leftTime = leftTime + ", ";
               leftTime += rs.getInt("time");
            }
            if (leftTime.compareTo("") != 0)
                leftTime = "(" + leftTime + ")";

            rs.close();

            stm.setInt(2, playModeRight);

            rs = stm.executeQuery();
            while (rs.next())
            {
                rightCount++;
                if (rightTime.compareTo("") != 0)
                    rightTime = rightTime + ", ";
               rightTime += rs.getInt("time");
            }
            if (rightTime.compareTo("") != 0)
                rightTime = "(" + rightTime + ")";

            rs.close();

            stm.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

//        return leftCount + " " + leftTime + " : " + rightCount + " " + rightTime;
        String[] result = {name, leftCount + " " + leftTime , rightCount + " " + rightTime};
        return result;
    }
    public String[] goalAnalyze(int gameID)
    {
        return analyze("Goal", gameID, PlayMode.PM_AfterGoal_Left, PlayMode.PM_AfterGoal_Right, false);
    }


    public String[] goalGickAnalyze(int gameID)
    {
        return analyze("Goal Kick", gameID, PlayMode.PM_GoalKick_Left, PlayMode.PM_GoalKick_Right, false);
    }

    public String[] offSideAnalyze(int gameID)
    {
        return analyze("Off Side", gameID, PlayMode.PM_OffSide_Left, PlayMode.PM_OffSide_Right, false);
    }

    public String[] connerKickAnalyze(int gameID)
    {
        return analyze("Conner Kick", gameID, PlayMode.PM_CornerKick_Left, PlayMode.PM_CornerKick_Right, false);
    }

    public String[] backPassAnalyze(int gameID)
    {
        return analyze("Back Pass", gameID, PlayMode.PM_Back_Pass_Left, PlayMode.PM_Back_Pass_Right, false);
    }

    public String[] freeKickAnalyze(int gameID)
    {
        return analyze("Free Kick", gameID, PlayMode.PM_FreeKick_Left, PlayMode.PM_FreeKick_Right, false);
    }

    public String[] freeKickFaultAnalyze(int gameID)
    {
        return analyze("Free Kick Fault", gameID, PlayMode.PM_Free_Kick_Fault_Left, PlayMode.PM_Free_Kick_Fault_Right, false);
    }

    public String[] kickInAnalyze(int gameID)
    {
        return analyze("Kick In", gameID, PlayMode.PM_KickIn_Left, PlayMode.PM_KickIn_Right, true);
    }

    public String[] ballOutAnalyze(int gameID)
    {
        return analyze("Ball Out", gameID, PlayMode.PM_Foul_BallOut_Left, PlayMode.PM_Foul_BallOut_Right, false);
    }

    public Object[][] analyze(int gameID)
    {
        if (connection == null)
            return null;

        Object[][] result = {goalAnalyze(gameID),
                            goalGickAnalyze(gameID),
                            offSideAnalyze(gameID),
                            backPassAnalyze(gameID),
                            connerKickAnalyze(gameID),
                            freeKickAnalyze(gameID),
                            freeKickFaultAnalyze(gameID),
                            kickInAnalyze(gameID),
                            ballOutAnalyze(gameID)
        };
        return result;
    }

    public static void main(String[] args) {
        DBAnalyzer dbanalyzer = new DBAnalyzer();
        System.out.println(dbanalyzer.goalAnalyze(17)[0] );
/*        System.out.println("goalAnalyze   " + dbanalyzer.goalAnalyze(17));
        System.out.println("goalGickAnalyze   " + dbanalyzer.goalGickAnalyze(17));
        System.out.println("backPassAnalyze   " + dbanalyzer.backPassAnalyze(17));
        System.out.println("ballOutAnalyze   " + dbanalyzer.ballOutAnalyze(17));
        System.out.println("connerKickAnalyze   " + dbanalyzer.connerKickAnalyze(17));
        System.out.println("freeKickAnalyze   " + dbanalyzer.freeKickAnalyze(17));
        System.out.println("freeKickFaultAnalyze   " + dbanalyzer.freeKickFaultAnalyze(17));
        System.out.println("kickInAnalyze   " + dbanalyzer.kickInAnalyze(17));
        System.out.println("offSideAnalyze   " + dbanalyzer.offSideAnalyze(17));
*/
    }
}
